
ALTER          proc RMA_report_proc

@product_id int,
@customer_id int,
@from_date varchar(15),
@to_date varchar(15),
@category varchar(3),
@status varchar(15),
@defaultPrompt varchar(50)


as
	declare @Customer_Name 		varchar(50)
	declare @RMA_Number		varchar(50)
	declare @PO_Number		varchar(50)
	declare @PO_LineItem_Number	int
	declare @Part_Number		varchar(50)
	declare @RMA_Requested_Date	datetime
	declare @Close_Date		datetime
	declare @Quantity		int
	declare @Unit_Price		float	
	declare @Extended_Price		float	
	declare @Problem_Description	varchar(500)
	declare @Remarks		varchar(500)
	declare @IS_Exchange		int
	declare @IS_Credit		int
	declare @IS_FA 			int

	declare @type			varchar(5)
	declare @RMA_STATUS 		varchar(15)

	declare @db_Customer_Name       varchar(50)
	declare @db_Part_Number         varchar(50)

	declare @lineItemCloseDate      datetime

	if (@from_date = '0')
	begin
		select @from_date = convert(varchar,min(RMA_Requested_Date),101) from RMA_Table
	end

	if (@to_date = '0')
	begin
		select @to_date = convert(varchar,max(RMA_Requested_Date),101) from RMA_Table
	end

	
	declare @results Table(CustomerName varchar(50), RmaNumber varchar(50), PoNumber varchar(50),PoLineItemNumber int,
		 PartNumber varchar(20),RequestedDate datetime,CloseDate datetime, Qty int,UnitPrice float,
		 ExtendedPrice float,ProblemDscription varchar(500),Remarks varchar(500),Type varchar(5), RmaStatus varchar(15))

	declare @RmaCursor cursor

	if (@product_Id <> '0' and @customer_Id <> '0'  )
	begin
		set @RmaCursor = cursor for
		select Customer_Name , RMA_Number , PO_Number , PO_LineItem_Number , RLIT.Part_Number , RMA_Requested_Date,
			RLIT.Close_Date , Quantity , Unit_Price , Problem_Description , Remarks , IS_Exchange , IS_Credit , IS_FA, RMA_STATUS , RLIT.Close_Date
		from RMA_Table RT, RMA_Line_Item_Table RLIT , Customer CUST , Product PRO	
		where 
			RT.RMA_ID = RLIT.RMA_ID
			and CUST.C_Name = RT.Customer_Name
			and PRO.Part_Number = RLIT.Part_Number
			and PRO.Product_ID = @product_Id
			and CUST.Customer_ID = @customer_Id
			and RMA_Requested_Date between @from_date and @to_date
	end
	else if (@product_Id <> '0' and @customer_Id = '0'  )
	begin	
		set @RmaCursor = cursor for
		select Customer_Name , RMA_Number , PO_Number , PO_LineItem_Number , RLIT.Part_Number , RMA_Requested_Date,
			RLIT.Close_Date , Quantity , Unit_Price , Problem_Description , Remarks , IS_Exchange , IS_Credit , IS_FA, RMA_STATUS , RLIT.Close_Date
		from RMA_Table RT, RMA_Line_Item_Table RLIT , Product PRO 	
		where 
			RT.RMA_ID = RLIT.RMA_ID
			and PRO.Part_Number = RLIT.Part_Number
			and PRO.Product_ID = @product_Id
			and RMA_Requested_Date between @from_date and @to_date	
	end
	else if (@product_Id = '0' and @customer_Id <> '0'  )
	begin
		set @RmaCursor = cursor for
		select Customer_Name , RMA_Number , PO_Number , PO_LineItem_Number , RLIT.Part_Number , RMA_Requested_Date,
			RLIT.Close_Date , Quantity , Unit_Price , Problem_Description , Remarks , IS_Exchange , IS_Credit , IS_FA, RMA_STATUS , RLIT.Close_Date
		from RMA_Table RT, RMA_Line_Item_Table RLIT , Customer CUST 
		where 
			RT.RMA_ID = RLIT.RMA_ID
			and CUST.C_Name = RT.Customer_Name
			and CUST.Customer_ID = @customer_Id
			and RMA_Requested_Date between @from_date and @to_date
	end
	else if (@product_Id = '0' and @customer_Id = '0'  )
	begin
		set @RmaCursor = cursor for
		select Customer_Name , RMA_Number , PO_Number , PO_LineItem_Number , RLIT.Part_Number , RMA_Requested_Date,
			RLIT.Close_Date , Quantity , Unit_Price , Problem_Description , Remarks , IS_Exchange , IS_Credit , IS_FA, RMA_STATUS , RLIT.Close_Date
		from RMA_Table RT, RMA_Line_Item_Table RLIT 
		where 
			RT.RMA_ID = RLIT.RMA_ID
			and RMA_Requested_Date between @from_date and @to_date
	end

	open @RmaCursor 

		fetch next from @RmaCursor
		into @Customer_Name , @RMA_Number , @PO_Number , @PO_LineItem_Number , @Part_Number , @RMA_Requested_Date,
			@Close_Date , @Quantity , @Unit_Price , @Problem_Description , @Remarks , @IS_Exchange , @IS_Credit , @IS_FA, @RMA_STATUS , @lineItemCloseDate

		while @@fetch_status = 0
		begin
			
			/* Modified by Prabhanjan (28 Jun 2004).
		
			if (@IS_Exchange = 0) 
			begin
				set @type = 'E'
			end
			else if (@IS_Credit = 0)
			begin
				set @type = 'C'
			end
			else if (@IS_FA = 0)
			begin
				set @type = 'F'
			end
			*/
			
			SET @type = ''

			IF (@IS_Exchange = 0) 
			BEGIN
				set @type = 'E'
			END
			
			IF (@IS_Credit = 0)
			BEGIN
				IF(@type <> '')
				BEGIN
					set @type = @type + '/' + 'C'
				END
				ELSE
				BEGIN
					set @type = 'C'
				END
			END
			
			IF (@IS_FA = 0)
			BEGIN
				IF(@type <> '')
				BEGIN
					set @type = @type + '/' + 'F'
				END
				ELSE
				BEGIN
					set @type = 'F'
				END

			END

			if ( @lineItemCloseDate is not null )
			begin
				set @RMA_STATUS = 'CLOSE'	
			end


			insert into @results(CustomerName , RmaNumber , PoNumber , PoLineItemNumber , PartNumber ,
			RequestedDate , CloseDate , Qty ,UnitPrice , ExtendedPrice , ProblemDscription , Remarks , Type, RmaStatus ) 

			values(@Customer_Name , @RMA_Number , @PO_Number , @PO_LineItem_Number , @Part_Number , @RMA_Requested_Date,
			@Close_Date , @Quantity , @Unit_Price , (@Quantity * @Unit_Price) , @Problem_Description , @Remarks , @type, @RMA_STATUS ) 	


			set @Customer_Name = null   
			set @RMA_Number = null  
			set @PO_Number = null 
			set @PO_LineItem_Number = null
			set @Part_Number = null
			set @RMA_Requested_Date = null
			set @Close_Date = null
			set @Quantity = null
			set @Unit_Price = null
			set @Problem_Description = null
			set @Remarks = null
			set @IS_Exchange = null
			set @IS_Credit = null
			set @IS_FA = null
			set @RMA_STATUS = null
			set @lineItemCloseDate = null	


			fetch next from @RmaCursor into @Customer_Name , @RMA_Number , @PO_Number , @PO_LineItem_Number , @Part_Number , @RMA_Requested_Date,
			@Close_Date , @Quantity , @Unit_Price , @Problem_Description , @Remarks , @IS_Exchange , @IS_Credit , @IS_FA, @RMA_STATUS , @lineItemCloseDate
		end

	close @RmaCursor
	deallocate @RmaCursor

	-- Modified by Prabhanjan (17 Jun 2004)
	IF @category <> '0' and @status <> '0'
	BEGIN
		select * from @results where Type = @category and RmaStatus = @status
	END
	ELSE IF @category <> '0' and @status = '0'
	BEGIN
		select * from @results where Type = @category
	END
	ELSE IF @category = '0' and @status <> '0'
	BEGIN
		select * from @results where RmaStatus = @status
	END
	ELSE IF @category = '0' and @status = '0'
	BEGIN
		select * from @results
	END
	ELSE
	BEGIN
		select * from @results
	END





